package com.ruoyi.sqlbuilder.execute.convert;

import cn.hutool.core.io.FileUtil;
import com.ruoyi.common.constant.Constants;
import com.ruoyi.common.utils.jdbc.JdbcUtil;
import com.ruoyi.sqlbuilder.domain.Columns;
import com.ruoyi.sqlbuilder.domain.KeyColumnUsage;
import com.ruoyi.sqlbuilder.domain.SbrDatabase;
import com.ruoyi.sqlbuilder.domain.Statistics;
import com.ruoyi.sqlbuilder.vo.SqlHandleVo;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.util.Assert;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * sql处理器
 *
 * @author Yanzy
 */
public abstract class SqlHandler implements ISqlHandler {

    private Logger log = LoggerFactory.getLogger(SqlHandler.class);

    /**
     * 分批量
     */
    protected static final int BATCH_SIZE = 1000;
    /**
     * 自增主键的表集合
     */
    protected CopyOnWriteArrayList<String> autoPrkTables = new CopyOnWriteArrayList<>();

    @Override
    public final List<String> selectTables(SqlHandleVo params) {
        Assert.notNull(params, "操作参数不能为空");
        // 查询数据库信息
        SbrDatabase dbInfo = params.getDbInfo();

        List<String> tables = new ArrayList<>();
        ResultSet res = null;
        Statement stmt = null;
        Connection connection = null;
        try {
            // 获取数据连接
            connection = JdbcUtil.getConnection(dbInfo.getHost(), dbInfo.getPort(), dbInfo.getDatabaseName(), dbInfo.getUserName(), dbInfo.getPassword(), dbInfo.getDriverType());
            stmt = connection.createStatement();
            // 查询存在的表信息
            String sql = String.format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = '%s' AND TABLE_TYPE = 'BASE TABLE'", dbInfo.getDatabaseName());
            stmt.setQueryTimeout(20);
            res = stmt.executeQuery(sql);
            while (res.next()) {
                tables.add(res.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            log.error("数据库查询异常: {}", e.getMessage());
        } finally {
            JdbcUtil.close(res, stmt, connection);
        }
        return tables;
    }

    @Override
    public final List<Columns> selectTableColumns(SqlHandleVo params, String tableName) {
        Assert.notNull(params, "操作参数不能为空");
        // 查询数据库信息
        SbrDatabase dbInfo = params.getDbInfo();
        List<Columns> columnsList = null;
        Connection connection = null;
        JdbcTemplate jdbcTemplate = null;
        try {
            String querySql = String.format("SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = '%s'  AND TABLE_SCHEMA = '%s'", tableName, dbInfo.getDatabaseName());
            // 获取数据连接
            connection = JdbcUtil.getConnection(dbInfo.getHost(), dbInfo.getPort(), dbInfo.getDatabaseName(), dbInfo.getUserName(), dbInfo.getPassword(), dbInfo.getDriverType());
            jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(connection, true));
            columnsList = jdbcTemplate.query(querySql, new BeanPropertyRowMapper<>(Columns.class));
        } catch (Exception e) {
            log.error("数据库查询异常: {}", e.getMessage());
        } finally {
            JdbcUtil.close(connection);
        }
        return columnsList;
    }

    @Override
    public final String selectTableValues(List<Columns> columns, SqlHandleVo params, String tableName, String filePath) {
        Assert.notNull(params, "操作参数不能为空");
        // 查询数据库信息
        SbrDatabase dbInfo = params.getDbInfo();
        Connection connection = null;
        JdbcTemplate jdbcTemplate = null;
        // 原子计数
        AtomicInteger rowNum = new AtomicInteger(0);
        RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();

        CopyOnWriteArrayList<String> insertSqlList = new CopyOnWriteArrayList<>();
        try {
            String sql = String.format("select * from `%s`.`%s`", dbInfo.getDatabaseName(), tableName);
            connection = JdbcUtil.getConnection(dbInfo.getHost(), dbInfo.getPort(), dbInfo.getDatabaseName(), dbInfo.getUserName(), dbInfo.getPassword(), dbInfo.getDriverType());
            jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(connection, true));
            jdbcTemplate.query(
                    // 执行查询
                    con -> {
                        log.debug("流式数据查询SQL -> {}", sql);
                        PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                        ps.setFetchSize(Integer.MIN_VALUE);
                        return ps;
                    },
                    // 每取到一条数据，执行一次下面的代码块
                    resultSet -> {
                        // 首次进入时给集合中添加头部信息
                        if (rowNum.get() == 0) {
                            insertSqlList.add(handleInsertHead(tableName));
                        }
                        Map<String, Object> row = rowMapper.mapRow(resultSet, rowNum.getAndIncrement());
                        String insertSql = handleTableValue(columns, row, tableName);
                        insertSqlList.add(insertSql);
                        if (insertSqlList.size() == BATCH_SIZE) {
                            FileUtil.appendLines(insertSqlList, filePath, Constants.UTF8);
                            insertSqlList.clear();
                        }
                    });
            log.info("处理 {}-表 insert数据库语句: {} 条", tableName, rowNum.get());
            // 加入脚部分
            if (rowNum.get() != 0) {
                insertSqlList.add(handleInsertFoot(tableName));
            }
            // 保证分批写入不漏掉数据
            FileUtil.appendLines(insertSqlList, filePath, Constants.UTF8);
            insertSqlList.clear();
        } finally {
            JdbcUtil.close(connection);
        }
        return filePath;
    }

    @Override
    public List<String> selectAndHandleForeignKey(SqlHandleVo params, String tableName) {
        Assert.notNull(params, "操作参数不能为空");
        // 查询数据库信息
        SbrDatabase dbInfo = params.getDbInfo();
        List<String> foreignKeys = new ArrayList<>();
        List<KeyColumnUsage> keyColumnUsages = null;
        Connection connection = null;
        JdbcTemplate jdbcTemplate = null;
        try {
            // sql排除主键
            String sql = String.format("SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA ='%s' AND TABLE_NAME = '%s' AND CONSTRAINT_NAME != 'PRIMARY'", dbInfo.getDatabaseName(), tableName);
            connection = JdbcUtil.getConnection(dbInfo.getHost(), dbInfo.getPort(), dbInfo.getDatabaseName(), dbInfo.getUserName(), dbInfo.getPassword(), dbInfo.getDriverType());
            jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(connection, true));
            keyColumnUsages = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KeyColumnUsage.class));
            if (CollectionUtils.isNotEmpty(keyColumnUsages)) {
                // 循环添加
                foreignKeys.add("-- ----------------------------");
                foreignKeys.add(String.format("-- Foreign by %s", tableName));
                foreignKeys.add("-- ----------------------------");
                for (KeyColumnUsage keyColumnUsage : keyColumnUsages) {
                    String foreignKeySql = handleForeignKey(keyColumnUsage);
                    if (StringUtils.isNotBlank(foreignKeySql)) {
                        foreignKeys.add(foreignKeySql);
                    }
                }
            }
        } finally {
            JdbcUtil.close(connection);
        }
        return foreignKeys;
    }

    /**
     * 查询并处理索引
     *
     * @param params    查询条件
     * @param tableName 表名
     * @return sqlList
     */
    @Override
    public List<String> selectAndHandleIndex(SqlHandleVo params, String tableName) {
        Assert.notNull(params, "操作参数不能为空");
        // 查询数据库信息
        SbrDatabase dbInfo = params.getDbInfo();
        List<String> indexKeys = new ArrayList<>();
        List<Statistics> statisticsList = null;
        Connection connection = null;
        JdbcTemplate jdbcTemplate = null;
        try {
            // sql排除主键
            String sql = String.format("SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA ='%s' AND TABLE_NAME = '%s' AND INDEX_NAME != 'PRIMARY'", dbInfo.getDatabaseName(), tableName);
            connection = JdbcUtil.getConnection(dbInfo.getHost(), dbInfo.getPort(), dbInfo.getDatabaseName(), dbInfo.getUserName(), dbInfo.getPassword(), dbInfo.getDriverType());
            jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(connection, true));
            statisticsList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Statistics.class));
            if (CollectionUtils.isNotEmpty(statisticsList)) {
                // 循环添加
                indexKeys.add("-- ----------------------------");
                indexKeys.add(String.format("-- Index by %s", tableName));
                indexKeys.add("-- ----------------------------");
                // 增加特殊处理  CREATE UNIQUE INDEX ACT_UNIQ_PROCDEF ON ACT_RE_PROCDEF(KEY_,TENANT_ID_, VERSION_);  这种索引
                // 分组
                Map<String, List<Statistics>> collect = statisticsList.stream().collect(Collectors.groupingBy(Statistics::getIndexName, Collectors.toList()));
                for (String key : collect.keySet()) {
                    // 判断一个组内成员个数选择执行方法
                    List<Statistics> group = collect.get(key);
                    if (group.size() == 1) {
                        indexKeys.add(handleIndex(group.get(0)));
                    } else {
                        indexKeys.add(handleIndexList(group));
                    }
                }
            }
        } finally {
            JdbcUtil.close(connection);
        }
        return indexKeys;
    }

    /**
     * 创建table sql 头部
     *
     * @param tableName   table名称
     * @param tableSchema 表架构
     * @return sql
     */
    @Override
    public abstract String createTableHead(String tableName, String tableSchema);

    /**
     * 创建table sql 身体部分
     *
     * @param columns 字段
     * @return sql
     */
    @Override
    public abstract String createTableBody(List<Columns> columns);


    @Override
    public String createTableSpecial(List<Columns> columns) {
        return StringUtils.EMPTY;
    }

    /**
     * 处理字段类型组装sql
     *
     * @param columns 字段
     * @return sql
     */
    @Override
    public abstract String handleColumnType(Columns columns);

    /**
     * 处理字段精度
     *
     * @param columns 字段
     * @return (num)
     */
    @Override
    public String handlePrecision(Columns columns) {


        if (StringUtils.equalsIgnoreCase(columns.getDataType(), "datetime") || StringUtils.equalsIgnoreCase(columns.getDataType(), "timestamp")) {
            if (columns.getDatetimePrecision() != null && columns.getDatetimePrecision() != 0) {

                return "(" + columns.getDatetimePrecision() + ")";
            }
        }

        // char varchar 赋予精度
        if (StringUtils.equalsIgnoreCase(columns.getDataType(), "char") || StringUtils.equalsIgnoreCase(columns.getDataType(), "varchar")) {
            if (columns.getCharacterMaximumLength() != null) {
                return "(" + columns.getCharacterMaximumLength() + ")";
            }
        }
        return StringUtils.EMPTY;
    }

    @Override
    public String handleNotNullPre(Columns columns) {
        if ("NO".equalsIgnoreCase(columns.getIsNullable())) {
            return "NOT NULL";
        }
        return StringUtils.EMPTY;
    }

    @Override
    public String handleDefaultValue(Columns columns) {
        if (columns.getColumnDefault() != null && !columns.getColumnDefault().isEmpty()) {
            if ("datetime".equalsIgnoreCase(columns.getDataType()) || "timestamp".equalsIgnoreCase(columns.getDataType())) {
                return "DEFAULT CURRENT_TIMESTAMP";
            }
            return "DEFAULT '" + columns.getColumnDefault() + "'";
        }
        return StringUtils.EMPTY;
    }

    /**
     * 处理表主键
     *
     * @param primaryKeys 主键集合
     * @return sql
     */
    @Override
    public abstract String handlePrimaryKey(List<String> primaryKeys);

    /**
     * 处理行数据 -> sql
     *
     * @param columns   所有列
     * @param values    所有列对应的值
     * @param tableName 表名
     * @return sql
     */
    @Override
    public abstract String handleTableValue(List<Columns> columns, Map<String, Object> values, String tableName);

    /**
     * 插入语句头部
     *
     * @param tableName 表名
     * @return sql
     */
    @Override
    public String handleInsertHead(String tableName) {
        return StringUtils.EMPTY;
    }

    ;

    /**
     * 插入语句尾部
     *
     * @param tableName 表名
     * @return sql
     */
    @Override
    public String handleInsertFoot(String tableName) {
        return StringUtils.EMPTY;
    }

    ;

    /**
     * 处理外键
     *
     * @param keyColumnUsage 外键对象
     * @return sql
     */
    @Override
    public String handleForeignKey(KeyColumnUsage keyColumnUsage) {
        if (keyColumnUsage.getPositionInUniqueConstraint() == null || keyColumnUsage.getReferencedTableSchema() == null
                || keyColumnUsage.getReferencedTableName() == null || keyColumnUsage.getReferencedColumnName() == null) {
            return StringUtils.EMPTY;
        }
        return String.format("ALTER TABLE `%s` ADD constraint `%s` FOREIGN KEY (`%s`) REFERENCES `%s` (`%s`);",
                keyColumnUsage.getTableName(), keyColumnUsage.getConstraintName(), keyColumnUsage.getColumnName(),
                keyColumnUsage.getReferencedTableName(), keyColumnUsage.getReferencedColumnName());
    }

    /**
     * 处理索引
     *
     * @param statistics 索引对象
     * @return sql
     */
    @Override
    public String handleIndex(Statistics statistics) {
        String sql;
        if (statistics.getNonUnique().compareTo(1L) > -1) {
            // 唯一索引
            sql = String.format("CREATE UNIQUE  INDEX `%s` ON `%s`.`%s`(`%s`);", statistics.getIndexName(), statistics.getTableSchema(),
                    statistics.getTableName(), statistics.getColumnName());
        } else {
            sql = String.format("CREATE INDEX `%s` ON `%s`.`%s`(`%s`);", statistics.getIndexName(), statistics.getTableSchema(),
                    statistics.getTableName(), statistics.getColumnName());
        }
        return sql;
    }

    @Override
    public String handleIndexList(List<Statistics> statistics) {
        return StringUtils.EMPTY;
    }
}
